/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;
import bentity.beRack;
import bentity.beUbicacion;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import visual.Seguridad.visualHelper;

/**
 *
 * @author cesar
 */
public class daoRack {
  public static final int E_OCUPADO = 14;
  public static final int E_RESERVADO = 37;
    public int getIdRack(int numFila, int numRack) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int idRack = -1;
        String sql = "SELECT ID_RACK FROM RACK WHERE NUMERO_FILA = ? AND NUMERO_RACK = ? ";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, numFila);
        ps.setInt(2, numRack);
        rs = ps.executeQuery();
        //rs.next();
        while (rs.next()){
            idRack = rs.getInt("ID_RACK");
        }
        
        rs.close();
        ps.close();
        conn.close();
        return idRack;

    }

    public int actualizarRack(int numFila,int numRack, int idRack) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "UPDATE RACK SET NUMERO_FILA = ?, NUMERO_RACK = ? WHERE ID_RACK = ?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, numFila);
        ps.setInt(2, numRack);
        ps.setInt(3,idRack);
        ps.executeUpdate();
        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;

    }

    public int actualizarTipoProdRack(int idRack, int idtipprod) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "UPDATE RACK SET ID_TIPO_PRODUCTO = ? WHERE ID_RACK = ?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, idtipprod);
        ps.setInt(2, idRack);
        ps.executeUpdate();
        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;

    }

    public int getIdRackxUbi(int idUbicacion) throws SQLException{

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int idRack = -1;
        String sql = "SELECT ID_RACK FROM UBICACION WHERE ID_UBICACION = ?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, idUbicacion);
        rs = ps.executeQuery();
        rs.next();
        idRack = rs.getInt(1);
        rs.close();
        ps.close();
        conn.close();
        return idRack;

    }

    public int getIdRackByXY(int x, int y) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int id=0;
        String sql = "SELECT ID_RACK FROM UBICACION WHERE POSICION_X=? AND POSICION_Y=?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, x);
        ps.setInt(2, y);
        rs=ps.executeQuery();
        while(rs.next()){
             id = rs.getInt(1);
        }
        ps.close();
        conn.close();
        return id;
    }

    public int fRegistrarRack(double ancho, double largo, int fila,int col, int pisos, int uxr) throws SQLException, Exception{
        //agregar los parámetros para cantidad de pisos y ubcaciones x piso
        Connection conn = daoHelper.dameConexion();


        String sql = "INSERT INTO RACK (cantidad_pisos, cantidad_ubicaciones, ancho, largo, estado_rack, id_almacen, numero_fila, numero_rack, id_rack) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, pisos);
        ps.setInt(2, uxr);
        ps.setDouble(3, ancho);
        ps.setDouble(4,largo);
        ps.setInt(5, 18);
        ps.setInt(6, 1);//siempre tendría que ser 1, ya q se tendría solo un almacén
        ps.setInt(7, fila);
        ps.setInt(8, col);
        int id_rack=daoHelper.getNextId("RACK","ID_RACK");
        ps.setInt(9, id_rack);
        ps.executeUpdate();
        ps.close();
        conn.close();
        return id_rack;

    }

    public void fRegistrarUbicacion(Connection conn, int piso, int ubi, int posX, int posY, int idrack) throws SQLException, Exception{


        String sql = "INSERT INTO UBICACION (NUMERO_PISO,NUMERO_UBICACION, POSICION_X, POSICION_Y, ID_RACK) VALUES (?, ?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, piso);
        ps.setInt(2, ubi);
        ps.setDouble(3, posX);
        ps.setDouble(4,posY);
        ps.setInt(5, idrack);
        ps.executeUpdate();
        ps.close();
        //conn.close();

    }

    public ArrayList<beRack> obtenerRacksActivos(int estado) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM RACK WHERE ESTADO_RACK = ? ";

        sql += " ORDER BY ID_RACK ";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,estado);

        rs = ps.executeQuery();
        ArrayList<beRack> racks = new ArrayList<beRack>();
        beRack r = null;
        while (rs.next()){
            r = new beRack(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(9),rs.getInt(8),rs.getInt(6));
            racks.add(r);
        }
        ps.close();
        conn.close();
        return racks;

    }

    public ArrayList<Integer> getEstados() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Integer> estados = new ArrayList<Integer>();
        String sql ="SELECT ID_DATO FROM DATOS_COMBO WHERE NOMBRE_COMBO = 'ESRACK'";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()){
            estados.add(rs.getInt(1));
        }
        ps.close();
        conn.close();
        return estados;

    }

    public ArrayList<beRack> obtenerRacksConSecs() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT NUMERO_FILA, NUMERO_RACK, ID_TIPO_PRODUCTO FROM RACK WHERE ID_TIPO_PRODUCTO IS NOT NULL ORDER BY ID_RACK";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();
        ArrayList<beRack> racks = new ArrayList<beRack>();
        beRack r = null;
        while (rs.next()){
            r = new beRack(rs.getInt(1),rs.getInt(2));
            racks.add(r);
        }
        ps.close();
        conn.close();
        return racks;

    }

    public ArrayList<beRack> obtenerRacksXUbica(ArrayList<beUbicacion> ubicaciones) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int ind = 0;
        ArrayList<beRack> racks = new ArrayList<beRack>();
         conn = daoHelper.dameConexion();
        for ( int i = 0; ubicaciones.size() > i; i++){
            String sql ="SELECT * FROM RACK WHERE ID_RACK = ?";
            
            ps = conn.prepareStatement(sql);
            int idRack = ubicaciones.get(i).getId_rack();
            ps.setInt(1, idRack);
            rs = ps.executeQuery();
            rs.next();
            beRack rack = new beRack(rs.getInt(8),rs.getInt(9));
            rack.setId_rack(rs.getInt(1));
            racks.add(rack);
            ind =1;
        }
        if (ind ==1) {
          ps.close();
          conn.close();
          rs.close();
        }
        return racks;


    }

    public ArrayList<String> obtenerEstados() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<String> estados = new ArrayList<String>();
        String sql ="SELECT NOMBRE_DATO FROM DATOS_COMBO WHERE NOMBRE_COMBO = 'ESRACK'";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()){
            estados.add(rs.getString(1));
        }
        ps.close();
        conn.close();
        return estados;

    }



    public int bloquear(int id,int estado) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        String sql ="UPDATE RACK SET ESTADO_RACK = ? WHERE ID_RACK = ?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);;
        ps.setInt(1,estado);
        ps.setInt(2,id);

        ps.executeUpdate();

        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;


    }

    public int desbloquear(int id,int estado) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        String sql ="UPDATE RACK SET ESTADO_RACK = ? WHERE ID_RACK = ?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);;
        ps.setInt(1,estado);
        ps.setInt(2,id);

        ps.executeUpdate();

        ps.close();
        conn.close();
        return daoHelper.TRANSACCION_EXITOSA;


    }

    public int obtenerUbsXRack() throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT CANTIDAD_UBICACIONES FROM RACK LIMIT 1";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        int cant=0;
        while (rs.next()){
            cant=rs.getInt(1);
        }
        conn.close();
        return cant;
    }

    public int obtenerPisosXRack() throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT CANTIDAD_PISOS FROM RACK LIMIT 1";

        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);

        rs = ps.executeQuery();

        int cant=0;
        while (rs.next()){
            cant=rs.getInt(1);
        }
        conn.close();
        return cant;
    }

    public beRack obtenerRack(int idRack) throws Exception{
        beRack rack=null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<String> estados = new ArrayList<String>();
        String sql ="SELECT RK.* FROM RACK RK WHERE RK.ID_RACK = ?";
        conn = daoHelper.dameConexion();
        
        ps = conn.prepareStatement(sql);
        ps.setInt(1,idRack);
        rs = ps.executeQuery();
        while (rs.next()){
            rack=new beRack(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(9),rs.getInt(8),rs.getInt(6));
            rack.setId_tipo_producto(rs.getInt(10));
        }
        ps.close();
        conn.close();
        return rack;
    }

     public boolean validarTabla(int estado,int estado1) throws SQLException{

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT COUNT(*) AS CONTEO FROM RACK WHERE ESTADO_RACK = ? OR ESTADO_RACK = ? ";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        int cont =-1;
        ps.setInt(1, estado);
        ps.setInt(2, estado1);
        rs=ps.executeQuery();
        rs.next();
        cont = rs.getInt(1);
       // rs.close();
        ps.close();
        conn.close();
        return (cont > 0);
     }

      public ArrayList<beUbicacion> dameUbicacionesXRack(int idRack) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<beUbicacion> ubicaciones = new ArrayList<beUbicacion>();
        String sql = "SELECT U.* FROM UBICACION U,RACK R WHERE U.ID_RACK = R.ID_RACK AND R.ID_RACK = ? ";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,idRack);
        rs=ps.executeQuery();
        while (rs.next()){
            beUbicacion ubi = new beUbicacion();
            ubi.setId_ubicacion(rs.getInt(1));
            ubicaciones.add(ubi);
        }
        rs.close();
        ps.close();
        conn.close();

        return ubicaciones;
    }

    public boolean validarUbiRack(ArrayList<beUbicacion> ubicaciones) throws SQLException{
       // int cont = ubicaciones.size();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int cont1 =0;
        conn = daoHelper.dameConexion();
        for (int i =0; i<ubicaciones.size();i++){
            String sql = "SELECT COUNT(*) AS CONTEO FROM UBICACION_X_PALLET WHERE ID_UBICACION = ? AND (ESTADOUBICACION = ? OR ESTADOUBICACION = ?)";            
            ps = conn.prepareStatement(sql);
            ps.setInt(1,ubicaciones.get(i).getId_ubicacion());
            ps.setInt(2,E_OCUPADO);
            ps.setInt(3,E_RESERVADO);
            rs=ps.executeQuery();
            rs.next();
            cont1 = cont1 + rs.getInt(1);
        }
      //  rs.close();
        ps.close();
        conn.close();

        return cont1 > 0;
    }

    public ArrayList<beRack> obtenerRaksXIncidencias() throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<beRack> racks = new ArrayList<beRack>();
        String sql = "SELECT R.*,I.DESCRIPCION_INCIDENTE FROM INCIDENTE I,RACK R,INCIDENTE_X_RACK IR WHERE R.ID_RACK = IR.ID_RACK AND IR.ID_INCIDENTE = I.ID_INCIDENTE";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();

        while (rs.next()){
            beRack r  = new beRack();
            r.setId_rack(rs.getInt(1));
            r.setCantUbicaciones(rs.getInt(3));
            r.setCantPiso(rs.getInt(2));
            r.setEstado(rs.getInt(6));
            r.setNumFila(rs.getInt(8));
            r.setNumRack(rs.getInt(9));
            r.setDescripcion(rs.getString(11));
            racks.add(r);


        }
        //rs.close();
        ps.close();
        conn.close();
        return racks;
    }
    public int eliminarIncidencias(int idRack) throws SQLException{
        Connection conn = null;
        PreparedStatement ps = null;
        String sql ="DELETE FROM INCIDENTE_X_RACK WHERE ID_RACK = ?";
        conn = daoHelper.dameConexion();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,idRack);
        ps.executeUpdate();
        ps.close();
        conn.close();
        return visualHelper.TRANSACCION_EXITOSA;

    }

}
